/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.insurance.cis.daos;

import com.insurance.cis.connection.CisConnection;
import com.insurance.cis.models.Product;
import com.insurance.cis.models.ProductCategory;
import com.insurance.cis.scos.ProductSearchCondition;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author TruongLQ
 */
public class ProductDao {

    public List<Product> getProducts(ProductSearchCondition sco) {
        List<Product> products = new ArrayList<>();
        ResultSet rs;
        final StringBuilder sql = new StringBuilder("select distinct prod.ProductID as ProductID, prod.ProductName as ProductName ")
                .append(", prod.ProductDesc as ProductDesc, prodCat.ProductCatID as ProductCatID, prodCat.ProductCatDesc as ProductCatDesc")
                .append(" from Product prod, ProductCategory prodCat ");
        try {
            if (sco.isEmptyCondition()) {
                sql.append(" where ")
                        .append("prod.ProductCatID=prodCat.ProductCatID order by prod.ProductID asc");
                Statement st = CisConnection.getInstance().getSqlConnection().createStatement();
                rs = st.executeQuery(sql.toString());
            } else {
                sql.append(" where prod.ProductCatID=prodCat.ProductCatID");
                Map<Integer, Object> parameters = new HashMap<>();
                int count = 0;
                if (!sco.getId().isEmpty()) {
                    sql.append(" and prod.ProductID = ? ");
                    parameters.put(++count, sco.getId());
                }
                if (!sco.getName().isEmpty()) {
                    sql.append(" and prod.ProductName like ?");
                    parameters.put(++count, "%" + sco.getName() + "%");
                }

                if (sco.getCategoryId() != null) {
                    sql.append(" and prod.ProductCatID = ?");
                    parameters.put(++count, sco.getCategoryId());
                }
                PreparedStatement pst = CisConnection.getInstance()
                        .getSqlConnection().prepareStatement(sql.toString());
                for (Integer pos : parameters.keySet()) {
                    pst.setObject(pos, parameters.get(pos));
                }

                rs = pst.executeQuery();
            }
            buildProducts(products, rs);
            return products;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDao.class.getName()).log(Level.SEVERE, null, ex);
            return products;
        }
    }

    private void buildProducts(List<Product> products, ResultSet rs) throws SQLException {
        while (rs.next()) {
            products.add(new Product(rs.getString("ProductID"),
                    rs.getString("ProductName"), rs.getString("ProductDesc"),
                    new ProductCategory(rs.getInt("ProductCatID"), rs.getString("ProductCatDesc"))));
        }
    }
}
